package jbl.service.impl;


import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jbl.dao.TdaDaoI;
import jbl.dao.ThjDaoI;
import jbl.dao.TxgDaoI;
import jbl.dao.ZtDaoI;
import jbl.io.excel.DaExcel;
import jbl.io.excel.ExcelTool;
import jbl.model.Code_zt;
import jbl.model.Tda;
import jbl.model.Thj;
import jbl.model.Tuser;
import jbl.model.Txg;
import jbl.pageModel.CountTda;
import jbl.pageModel.Da;
import jbl.pageModel.DataGrid;
import jbl.service.DaServiceI;
import jbl.util.JblTool;
import jbl.util.Json;
import jbl.util.Message;

import org.apache.commons.fileupload.FileItem;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service("daService")
public class DaServiceImpl implements DaServiceI {
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(DaServiceImpl.class);
	
	private TdaDaoI daDao;

	private TxgDaoI xgDao;
	
	private ThjDaoI hjDao;
	
	
	private ZtDaoI ztDao;
	
	@Autowired
	public void setZtDao(ZtDaoI ztDao) {
		this.ztDao = ztDao;
	}
	
	@Autowired
	public void setXgDao(TxgDaoI xgDao) {
		this.xgDao = xgDao;
	}
	@Autowired
	public void setHjDao(ThjDaoI hjDao) {
		this.hjDao = hjDao;
	}
	
	
	@Autowired
	public void setdaDao(TdaDaoI daDao) {
		this.daDao = daDao;
	}

	@Override
	public DataGrid dataGrid(Da da) {
		// TODO Auto-generated method stub
		DataGrid dg = new DataGrid();
		Tuser tuser = JblTool.getTuser();
		try {
			List<Da> daList = new ArrayList<Da>();
			Map<String, Object> params = new HashMap<String, Object>();
			String hql = " from Tda t where 1=1 ";
			if(!tuser.getTuserGroup().getId().equals("7a0aaea7eac94b44bde3a2b9707dc8fc")){
				hql += " and t.zt.flag1 = 'T' ";
			}
			
			hql = addWhere(hql , da, params);
			String totalHql = "select count(*) " + hql;
			hql = addOrder(hql , da);
			List<Tda> list = daDao.find(hql, params, da.getPage(), da.getRows());
			if(null != list && list.size() > 0){
				for(Tda t : list){
					Da da1 = new Da();
					BeanUtils.copyProperties(t, da1);
					processFK(t, da1);
					daList.add(da1);
				}
			}
			
			dg.setRows(daList);
			dg.setTotal(daDao.count(totalHql,params));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			logger.error("查询da信息error:"+e.getMessage());
		}
		return dg;
	}
	
	private String addOrder(String hql , Da da){
		if(null != da.getSort()){
			if(da.getSort().equals("xgList")){
				da.setSort("txg.id");
			}
			if(da.getSort().equals("ztList")){
				da.setSort("zt.id");
			}
			if(da.getSort().equals("xgList")){
				da.setSort("txg.id");
			}
			if(da.getSort().equals("gsList")){
				da.setSort("gs.id");
			}
			if(da.getSort().equals("lbList")){
				da.setSort("lb.id");
			}
			if(da.getSort().equals("daList")){
				da.setSort("da.id");
			}
			if(da.getSort().equals("addUserName")){
				da.setSort("tuser.id");
			}
			if(da.getSort().equals("editUserName")){
				da.setSort("tuser1.id");
			}
			hql += " order by t."+da.getSort() +" " + da.getOrder();
		}
		return hql;
	}
	
	private String addWhere(String hql , Da da , Map<String, Object> params){
		if(da.getId()!=null && !da.getId().trim().equals("")){
			hql+=" and t.id like '%%"+da.getId().trim()+"%%'";
		}
		if(da.getIded()!=null && !da.getIded().trim().equals("")){
			hql+=" and t.da.id=:ided ";
			params.put("ided", da.getIded());
		}
		if(da.getAddUserId()!=null && !da.getAddUserId().trim().equals("")){
			hql+=" and t.tuser.id=:userId ";
			params.put("userId", da.getAddUserId());
		}
		if(da.getZtId()!=null && !da.getZtId().trim().equals("")){
			hql+=" and t.zt.id=:ztId ";
			params.put("ztId", da.getZtId());
		}
		if(da.getGsmc()!=null && !da.getGsmc().trim().equals("")){
			hql+=" and t.gsmc like '%%"+da.getGsmc().trim()+"%%'";
		}
		if(da.getLbmc()!=null && !da.getLbmc().trim().equals("")){
			hql+=" and t.lbmc like '%%"+da.getLbmc().trim()+"%%'";
		}
		if(da.getStartCjsj()!=null&&!da.getStartCjsj().equals("")){
			hql+=" and t.cjsj>=:start";
			params.put("start",da.getStartCjsj());
		}
		if(da.getEndCjsj()!=null&&!da.getEndCjsj().equals("")){
			hql+=" and t.cjsj<=:end";
			params.put("end",da.getEndCjsj());
		}
		if(null != da.getXgId() && !"".equals(da.getXgId())){
			hql+=" and t.txg.id=:xgId";
			params.put("xgId",da.getXgId());
		}
		return hql;
	}
	
	private void processFK(Tda t , Da da){
		Txg xg = t.getTxg();
		if(null != xg){
			da.setXgId(xg.getId());

		}
		Tuser tuser = t.getTuser();
		if(null != tuser){
			da.setAddUserId(tuser.getId());
			da.setAddUserName(tuser.getName());
		}
		Tuser tuser1 = t.getTuser1();
		if(null != tuser1){
			da.setEditUserName(tuser1.getName());
		}

		
		Code_zt zt = t.getZt();
		if(null != zt){
			List<Object> list = new ArrayList<Object>();
			list.add(zt.getZt());
			list.add(zt.getId());
			
			
			da.setZtList(list);
		}
		
		Tda da1 = t.getDa();
		if(null != da1){
//			List<Object> list = new ArrayList<Object>();
//			list.add(da1.getId());
//			da.setDaList(list);
			da.setIded(da1.getId());
		}
	}

	@Override
	public Json delete(String ids) {
		Json j = new Json();
		try{
			if(null != ids){
				for(String id : ids.split(",")){
					if(!"".equals(id.trim())){
						Tda tda = daDao.get(Tda.class, id);
						Code_zt zt = new Code_zt();
						zt.setId("5");
						tda.setZt(zt);
						daDao.update(tda);
					}
				}
			}
			j.setMsg("删除成功");
			j.setSuccess(true);
		}catch(Exception e){
			e.printStackTrace();
			j.setMsg("删除失败");
		}
		return j;
	}

	@Override
	public Json edit(Da da) {
		// TODO Auto-generated method stub
		Json j = new Json();
		try {
			Tda tda = new Tda();
			BeanUtils.copyProperties(da, tda);
			addWj(da, tda);
			tda.setTuser1(JblTool.getTuser());
			if(null != da.getAddUserId()){
				Tuser tuser = new Tuser();
				tuser.setId(da.getAddUserId());
				tda.setTuser(tuser);
			}
			daDao.update(tda);
			j.setMsg("更新成功");
			j.setSuccess(true);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			logger.error("更新失败:"+e.getMessage());
			j.setMsg("更新失败");
		}
		return j;
	}

	@Override
	public Json add(Da da) {
		Json j = new Json();
		try {
			Tda tda = daDao.get(Tda.class, da.getId());
			if(null != tda){
				j.setMsg("添加失败：改编号档案已存在！");
				return j;
			}else{
				tda = new Tda();
				BeanUtils.copyProperties(da, tda);
				addWj(da, tda);
				tda.setTuser(JblTool.getTuser());
				daDao.save(tda);
				j.setMsg("添加成功");
				j.setSuccess(true);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			logger.error("添加失败:"+e.getMessage());
			j.setMsg("添加失败");
		}
		return j;
	}
	
	private void addWj(Da da , Tda tda){
		if(null != da.getXgId() && !"".equals(da.getXgId())){
			Txg txg = new Txg();
			txg.setId(da.getXgId());
			tda.setTxg(txg);
		}

		List<Object> ztList = da.getZtList();
		if(null != ztList){
			if(null != ztList.get(0).toString() && !"".equals(ztList.get(0).toString().trim())){
				Code_zt zt = new Code_zt();
				zt.setId(ztList.get(0).toString());
				
				tda.setZt(zt);
			}
		}

		if(null != da.getIded() && !"".equals(da.getIded())){
			Tda tda1 = new Tda();
			tda1.setId(da.getIded());
			tda.setDa(tda1);
		}
		if(null == da.getCjsj() || "".equals(da.getCjsj())){
			tda.setCjsj(new  Timestamp(new  Date().getTime()));
		}
	}



	@Override
	public Json upDaFile(Da da) {
		Json j = new Json();
		
		Workbook book = null; 
		try {
			List<FileItem> list = ExcelTool.getItemFile();
			for(FileItem item:list){
				if("da_upload_excel".equals(item.getFieldName().trim())){
					da.setFilename(item.getName());
					try {
						book = ExcelTool.createWorkBook(item.getInputStream(), da.getFilename());//解析上传文件Excel
						 Sheet sheet =  book.getSheetAt(0);	
						 List<Tda> daList = new ArrayList<Tda>();
						 List<Code_zt> ztList = ztDao.find("from Code_zt c");
						 Code_zt zt1 = ztDao.get("from Code_zt z where z.zt='已入库' ");
				         for (int i = 1; i <= sheet.getLastRowNum(); i++) { //解析成功  输出
				             Row ros = sheet.getRow(i); 
				             String ros1 = String.valueOf(ros.getCell(1)).trim();	
				             String ros2 = String.valueOf(ros.getCell(2)).trim();
				             String ros3 = String.valueOf(ros.getCell(3)).trim();
				             String ros4 = String.valueOf(ros.getCell(4)).trim();
				            
				             String ros5 = String.valueOf(ros.getCell(5)).trim();
				             String ros6 = String.valueOf(ros.getCell(6)).trim();
				             String ros7 = String.valueOf(ros.getCell(7)).trim();
				             String ros8 = String.valueOf(ros.getCell(8)).trim();
				             String id = "";
				             Tda tda = new Tda();
				             if(!"null".equals(ros1) && !"".equals(ros1)){
				            	 try {
				            		 id = Message.doubleFormat.format(ros.getCell(1).getNumericCellValue());
								} catch (Exception e) {
									// TODO: handle exception
									e.printStackTrace();
									j.setMsg("档案编号(条码号)-"+ros1+"-格式不正确：第2列,第"+(i+1) +"行!");
				     				return j;
								}
				            	 
				            	 Tda tda1 = daDao.get(Tda.class, id);
				     			if(null != tda1){
				     				j.setMsg("档案编号(条码号)-"+id+"-已存在：第2列,第"+(i+1)+"行!");
				     				return j;
				     			}
				            	 tda.setId(id);
				             }else{
				            	 j.setMsg("档案编号(条码号)不能为空：第2列,第"+(i+1)+"行!");
				            	 return j; 
				             }

				             
				             if("null".equals(ros2) && "".equals(ros2)){
				            
				            	 j.setMsg("货架编号不能为空：第2列,第"+(i+1)+"行!");
				            	 return j; 
				             }else{
				            	 String xgId = "";
				            	 try {
				            		 xgId = Message.doubleFormat.format(ros.getCell(2).getNumericCellValue());
								} catch (Exception e) {
									// TODO: handle exception
									e.printStackTrace();
									j.setMsg("箱柜编号-"+ros2+"-格式不正确：第2列,第"+(i+1) +"行!");
				     				return j;
								}
				            	// xgId =ros.getCell(2).getStringCellValue();
					             Txg xg = xgDao.get(Txg.class, xgId);
					             
					             if(null == xg){
					            	 j.setMsg("箱柜编号-"+xgId+"-数据库未记录：第3列,第"+(i+1)+"行!");
					            	 return j;
					             }else{
					            	 tda.setTxg(xg);
					             }
				             }
	
				             if(!"null".equals(ros3) && !"".equals(ros3)){
				            	 Code_zt zt = null;
				            	 for(Code_zt t : ztList){
				            		 if(ros3.equals(t.getZt())){
				            			 zt = t;
				            		 }
				            	 }
				            	 if(null == zt){
				            		 j.setMsg("状态-"+ros3+"-数据库未记录：第4列,第"+(i+1)+"行!");
					            	 return j; 
				            	 }else{
				            		 tda.setZt(zt);
				            	 }

				             }else{
				            	 tda.setZt(zt1); 
				             }
				             if(!"null".equals(ros4) && !"".equals(ros4)){
				            	tda.setGsmc(ros4);
				             }
				             if(!"null".equals(ros5) && !"".equals(ros5)){
					            	tda.setLbmc(ros5);
					           }
				             if(!"null".equals(ros6) && !"".equals(ros6)){
				            	 String ided = "";
				            	 try {
				            		 ided = Message.doubleFormat.format(ros.getCell(6).getNumericCellValue());
								} catch (Exception e) {
									// TODO: handle exception
									e.printStackTrace();
									j.setMsg("档案夹编号-"+ros6+"-格式不正确：第7列,第"+(i+1)+"行!");
				     				return j;
								}
				            	 
				            	 Tda tda1 = daDao.get(Tda.class, ided);
				     			if(null == tda1){
				     				j.setMsg("档案夹编号-"+ided+"-数据库未记录：第7列,第"+(i+1)+"行!");
				     				return j;
				     			}
				            	 tda.setDa(tda1);
				             }
				             if(!"null".equals(ros7) && !"".equals(ros7)){
				            	 tda.setBz(ros7);
				             }
				             if(!"null".equals(ros8) && !"".equals(ros8)){
				            	 try {
				            		 tda.setCjsj(Timestamp.valueOf(ros8));
								} catch (Exception e) {
									j.setMsg("创建日期-"+ros8+"-格式不正常：第9列,第"+(i+1)+"行!");
									return j;
								}
				             }else{
				            	 tda.setCjsj(Timestamp.valueOf(Message.timeFormat.format(new Date())));
				             }
				             daList.add(tda);
				         } 
				         for(Tda t : daList){
				        	 t.setTuser(JblTool.getTuser());
				        	 daDao.save(t);
				         }
				         j.setMsg("导入成功");
				         j.setSuccess(true);
					} catch (FileNotFoundException e) {
						e.printStackTrace();
						j.setMsg("文件未找到!");
						return j;
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
						j.setMsg("IO中断!");
						return j;
					}
				}
			}
			
		} catch (Exception e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			j.setMsg("导入失败");
		}
		
		return j;
	}
	
	@Override
	public List<DaExcel> getDaExcelList(Da da) {
		List<DaExcel> daExcelList = new ArrayList<DaExcel>();
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			String hql = " from Tda t where 1=1 ";
			hql = addWhere(hql , da, params);
			List<Tda> list = daDao.find(hql, params);
			int xh = 1;
			if(null != list && list.size() > 0){
				for(Tda t : list){
					DaExcel daExcel = new DaExcel();
					BeanUtils.copyProperties(t, daExcel);
					processFK1(t, daExcel);
					daExcel.setNumXh(xh);
					
					daExcelList.add(daExcel);
					xh++;
				}
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			logger.error("查询da信息error:"+e.getMessage());
		}
		return daExcelList;
	}
	
	private void processFK1(Tda t , DaExcel da){
		Txg xg = t.getTxg();
		if(null != xg){
			da.setXgId(xg.getId());
			da.setHjNumberId(xg.getThj().getNumberID());
			da.setKfNumberId(xg.getThj().getTkf().getNumberID());
		}
		
		
		Code_zt zt = t.getZt();
		if(null != zt){
			da.setZtName(zt.getZt());
		}
		
		Tda da1 = t.getDa();
		if(null != da1){
			da.setIded(da1.getId());
		}
	}

	
	@Override
	public List<Da> comboboxInfo() {
		List<Da> daList = new ArrayList<Da>();
		try {
			
			String hql = " from Tda t where t.da is null ";
			List<Tda> list = daDao.find(hql);
			if(null != list && list.size() > 0){
				for(Tda t : list){
					Da da1 = new Da();
					BeanUtils.copyProperties(t, da1);
					processFK(t, da1);
					daList.add(da1);
				}
			}

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			logger.error("查询货架信息error:"+e.getMessage());
		}
		return daList;
	}

	@Override
	public List<CountTda> getCountData() {
		List<CountTda> countTdaList = new ArrayList<CountTda>();
		try {
			String hjHql = "from Thj t1 order by t1.cjsj Desc";
			List<Thj> hjList = hjDao.find(hjHql, 10, 1);
			if(null != hjList && hjList.size() > 0){
				Map<String, Object> params1 = new HashMap<String, Object>();
				String xgHql = "from Txg t where t.thj=:thj order by t.cjsj desc";
				params1.put("thj", hjList.get(0));
				List<Txg> xgList = xgDao.find(xgHql,params1);
				
				if(null != xgList && xgList.size() > 0){
					for(Txg xg : xgList){
						String daHql = "Select count(*) from Tda t where t.txg=:txg ";
						Map<String, Object> params = new HashMap<String, Object>();
						params.put("txg", xg);
						Long countNum = daDao.count(daHql,params);
						CountTda countTda = new CountTda();
						countTda.setCountNum(countNum);
						countTda.setXgId(xg.getId());
						countTda.setNormNum(toNormNum(countNum));
						
						countTdaList.add(countTda);
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return countTdaList;
	}
	
	

	private long toNormNum(long countNum){
		if(countNum == 0){
			return 5;
		}else if(countNum < (Message.MAX_XG_NUM*0.25)){
			return 4;
		}else if(countNum < (Message.MAX_XG_NUM*0.5)){
			return 3;
		}else if(countNum < (Message.MAX_XG_NUM*0.75)){
			return 2;
		}else if(countNum <= Message.MAX_XG_NUM){
			return 1;
		}
		return 5;
	}
	
}
